package com.zhiyou100.blog.dao.impl;

import com.zhiyou100.blog.dao.UserDao;
import com.zhiyou100.blog.entity.User;
import com.zhiyou100.blog.utils.JDBCUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.junit.Test;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.List;

/**
 * @author shkstart
 * @create 2021-10-22 16:42
 */
public class UserDaoImpl implements UserDao {
    @Test
    public void test1() {
//        System.out.println(getListOrderByMaxScore());
//        System.out.println(getUserLvFenById(4));
//        System.out.println(getListFenById(4));
        System.out.println(getFenCountByStartId(4));
    }

    @Override
    public long getFenCountByStartId(int uStartId) {
        try (Connection conn = JDBCUtils.getConnection();) {
            QueryRunner runner = new QueryRunner();
            String sql = "SELECT COUNT(*) FROM tab_fensi WHERE uid_star =?;";
            ScalarHandler<Object> handler = new ScalarHandler<>();
            return (Long) runner.query(conn, sql, handler, uStartId);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0L;
    }

    @Override
    public List<User> getListFenById(int uStartId) {
        try (Connection conn = JDBCUtils.getConnection();) {
            QueryRunner runner = new QueryRunner();
            String sql = "SELECT u.uid,u.uname,u.upwd,u.usex," +
                    "u.uchange_time uchangeTime,u.uphoto,u.uscore,u.ustatus " +
                    "FROM tab_user u,tab_fensi f " +
                    "WHERE f.uid_fen=u.uid AND f.uid_star= ?;";
            BeanListHandler<User> listHandler = new BeanListHandler<>(User.class);
            return runner.query(conn, sql, listHandler, uStartId);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public List<User> getUserLvById(int uid) {
        try (Connection conn = JDBCUtils.getConnection();) {
            QueryRunner runner = new QueryRunner();

            String sql = "SELECT DISTINCT u.uid,u.uname,u.upwd,u.usex," +
                    "u.uchange_time uchangeTime,u.uphoto,u.uscore," +
                    "u.ustatus," +
                    "CASE " +
                    "WHEN u.uscore > l.lscore_low AND u.uscore < l.lscore_high " +
                    "THEN  l.lname " +
                    "END lv " +
                    "FROM tab_user u,tab_level l " +
                    "WHERE  u.uid=?;";

            BeanListHandler<User> handler = new BeanListHandler<>(User.class);
            return runner.query(conn, sql, handler, uid);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }


    @Override
    public User getOneByName(String name) {
        try (Connection conn = JDBCUtils.getConnection();) {
            QueryRunner runner = new QueryRunner();
            String sql = "SELECT u.uid,u.uname,u.upwd,u.usex,u.`uchange_time` uchangeTime,u.uphoto,u.uscore,u.ustatus\n" +
                    "  FROM tab_user u where u.uname=?;";
            //ResultSetHandler<T> rsh 结果集的处理器
            BeanHandler<User> handler = new BeanHandler<>(User.class);
            return runner.query(conn, sql, handler, name);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }


    @Override
    public User getOneById(int uid) {
        try (Connection conn = JDBCUtils.getConnection();) {
            QueryRunner runner = new QueryRunner();
            String sql = "SELECT u.uid,u.uname,u.upwd,u.usex,u.`uchange_time` uchangeTime,u.uphoto,u.uscore,u.ustatus FROM tab_user u where u.uid=?";
            //ResultSetHandler<T> rsh 结果集的处理器
            BeanHandler<User> handler = new BeanHandler<>(User.class);
            return runner.query(conn, sql, handler, uid);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    @Override
    public int addOne(User user) {
        try (Connection conn = JDBCUtils.getConnection()) {
            QueryRunner runner = new QueryRunner();
            String sql = "insert into tab_user(uname,upwd,uphoto,usex,uscore,uchange_time,ustatus) values(?,?,?,?,?,?,?)";
            //ResultSetHandler<T> rsh 结果集的处理器
            return runner.update(conn, sql, user.getUname(),
                    user.getUpwd(), user.getUphoto(),
                    user.getUsex(), user.getUscore(),
                    new Timestamp(user.getUchangeTime().getTime()),
                    user.getUstatus());
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return 0;
    }

    @Override
    public int updateUser(User user) {
        try (Connection conn = JDBCUtils.getConnection()) {
            QueryRunner runner = new QueryRunner();
            String sql = "update tab_user set uname=?,upwd=?,uphoto=?,usex=?,uchange_time=? where uid=?";
            //ResultSetHandler<T> rsh 结果集的处理器
            return runner.update(conn, sql, user.getUname(),
                    user.getUpwd(), user.getUphoto(),
                    user.getUsex(),
                    new Timestamp(user.getUchangeTime().getTime()),
                    user.getUid());
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return 0;
    }


    @Override
    public List<User> getListOrderByMaxScore() {

        try (Connection conn = JDBCUtils.getConnection();) {
            QueryRunner runner = new QueryRunner();

            String sql = "SELECT u.uid,u.uname,u.upwd,u.usex,u.uchange_time uchangeTime,u.uphoto,u.uscore,u.ustatus FROM tab_user u order by u.uscore desc limit ?,?";
            //ResultSetHandler<T> rsh 结果集的处理器
            //使用BeanListHandle的子类BeanHandler<T>
            BeanListHandler<User> handler = new BeanListHandler<>(User.class);

            List<User> list = runner.query(conn, sql, handler, 0, 4);
            return list;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    @Override
    public int getCount() {
        try (Connection conn = JDBCUtils.getConnection();) {
            QueryRunner runner = new QueryRunner();

            String sql = "select count(*) from tab_user;";
            //ResultSetHandler<T> rsh 结果集的处理器
            ScalarHandler<Object> handler = new ScalarHandler<>();
            return (int) runner.query(conn, sql, handler);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return 0;
    }

}
